////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   PO
// AUTHOR           :   Victor.zheng 
// DATE CREATE      :   2005/10/25
// DESCRIPTION      :   a DAO file for the function, PO
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/10/25   Victor.zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.login.poEnquiry.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.erp.app.common.ProjectConstants;
import com.erp.app.login.poEnquiry.criteria.PoEnquirySrchCriteria;
import com.erp.app.login.poEnquiry.pk.PoEnquiryPk;
import com.erp.app.login.poEnquiry.vo.PoEnquiryVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor.zheng 
* @version 1.0     2005/10/25
* @since           FrameWork 1.0
*
*/
public class PoEnquiryDao extends SimplePageEditStyleDao {
    
    public void setMaxRows(Statement stmt) throws Exception{
        stmt.setMaxRows(ProjectConstants.DB_ENQUIRY_ROW_NUM);
    }

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {

		PoEnquirySrchCriteria criteria = (PoEnquirySrchCriteria) obj;
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY PO_DATE DESC, PO_NO";

		String sql = "SELECT COMPANY_CODE, PO_NO, PO_DATE, VENDOR_CODE "
			+ "FROM T_PO_HDR "
			+ whereClause
			+ groupByClause
			+ orderByClause;

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	private String toWhereClause(PoEnquirySrchCriteria crit) {

		PoEnquirySrchCriteria criteria = (PoEnquirySrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode())) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append("COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}

        if (!CommonUtil.isEmpty(criteria.getSrchPoNoFr())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("PO_NO >= '"
                + criteria.getSrchPoNoFr().toUpperCase().trim() + "' ");
        }
        if (!CommonUtil.isEmpty(criteria.getSrchPoNoTo())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("PO_NO <= '"
                + criteria.getSrchPoNoTo().toUpperCase().trim() + "' ");
        }
        if (!CommonUtil.isEmpty(criteria.getSrchPoDateFr())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" PO_DATE >= TO_DATE('"
                    + criteria.getSrchPoDateFr() + "', '"
                    + ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
        }
        if (!CommonUtil.isEmpty(criteria.getSrchPoDateTo())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" PO_DATE <= TO_DATE('"
                    + criteria.getSrchPoDateTo() + "', '"
                    + ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
        }
        
		return whereClause.toString();
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {
		String sql = "";

//		String sql = "INSERT INTO T_PO_HDR ("
//			+ "COMPANY_CODE, PO_NO, SUPL_CODE, BILL_CONTACT_PERSON, BILL_ADDR1, "
//			+ "BILL_ADDR2, BILL_ADDR3, BILL_ADDR4, BILL_TEL, BILL_FAX, "
//			+ "BILL_EMAIL, PO_DATE, CUST_CODE, SHIP_CONTACT_PERSON, SHIP_ADDR1, "
//			+ "SHIP_ADDR2, SHIP_ADDR3, SHIP_ADDR4, SHIP_TEL, SHIP_FAX, "
//			+ "SHIP_EMAIL, STATUS, SUPL_REF_NO, COUNTRY_OF_ORIGIN, PORT_OF_LOADING, "
//			+ "DESTINATION, SHIP_VIA, PAY_TERM, HANDLED_BY, CCY, "
//			+ "TOTAL_AMT, SHIP_ROUTE, FREIGHT_FORWARD, REMARK1, REVISION, "
//			+ "CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, SO_NO, "
//			+ "SHIP_COUNTRY_CODE, SHIP_AREA_CODE, BILL_COUNTRY_CODE, BILL_AREA_CODE, SHIP_DATE, "
//			+ "DEPT_CODE, TRADE_TERMS, EXCHANGE_RATE, REMARK2, PROJECT_CODE, "
//			+ "DIVISION_DESC, PREPARED_BY, ASM_FORWARDER) "
//			+ "VALUES ("
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, SYSDATE, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?, ?, ?, "
//			+ "?, ?, ?)";
//
		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

//		PoEnquiryVo vo = (PoEnquiryVo) obj;
//		int i = 1;
//
//		pStmt.setString(i++, vo.getCompanyCode());
//		pStmt.setString(i++, vo.getPoNo());
//		setNullableString(pStmt, i++, vo.getSuplCode());
//		setNullableString(pStmt, i++, vo.getBillContactPerson());
//		setNullableString(pStmt, i++, vo.getBillAddr1());
//		setNullableString(pStmt, i++, vo.getBillAddr2());
//		setNullableString(pStmt, i++, vo.getBillAddr3());
//		setNullableString(pStmt, i++, vo.getBillAddr4());
//		setNullableString(pStmt, i++, vo.getBillTel());
//		setNullableString(pStmt, i++, vo.getBillFax());
//		setNullableString(pStmt, i++, vo.getBillEmail());
//		setNullableDate(pStmt, i++, vo.getPoDate());
//		setNullableString(pStmt, i++, vo.getCustCode());
//		setNullableString(pStmt, i++, vo.getShipContactPerson());
//		setNullableString(pStmt, i++, vo.getShipAddr1());
//		setNullableString(pStmt, i++, vo.getShipAddr2());
//		setNullableString(pStmt, i++, vo.getShipAddr3());
//		setNullableString(pStmt, i++, vo.getShipAddr4());
//		setNullableString(pStmt, i++, vo.getShipTel());
//		setNullableString(pStmt, i++, vo.getShipFax());
//		setNullableString(pStmt, i++, vo.getShipEmail());
//		setNullableString(pStmt, i++, vo.getStatus());
//		setNullableString(pStmt, i++, vo.getSuplRefNo());
//		setNullableString(pStmt, i++, vo.getCountryOfOrigin());
//		setNullableString(pStmt, i++, vo.getPortOfLoading());
//		setNullableString(pStmt, i++, vo.getDestination());
//		setNullableString(pStmt, i++, vo.getShipVia());
//		setNullableString(pStmt, i++, vo.getPayTerm());
//		setNullableString(pStmt, i++, vo.getHandledBy());
//		setNullableString(pStmt, i++, vo.getCcy());
//		setNullableBigDecimal(pStmt, i++, vo.getTotalAmt());
//		setNullableString(pStmt, i++, vo.getShipRoute());
//		setNullableString(pStmt, i++, vo.getFreightForward());
//		setNullableString(pStmt, i++, vo.getRemark1());
//		setNullableString(pStmt, i++, vo.getRevision());
//		setNullableString(pStmt, i++, vo.getCreateUser());
//
//		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
//			setNullableTimestamp(pStmt, i++, vo.getCreateDate());
//
//		setNullableString(pStmt, i++, vo.getUpdateUser());
//
//		if (ProjectConstants.DB_OP_INSERT.equals(opType))
//			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
//		setNullableString(pStmt, i++, vo.getSoNo());
//		setNullableString(pStmt, i++, vo.getShipCountryCode());
//		setNullableString(pStmt, i++, vo.getShipAreaCode());
//		setNullableString(pStmt, i++, vo.getBillCountryCode());
//		setNullableString(pStmt, i++, vo.getBillAreaCode());
//		setNullableDate(pStmt, i++, vo.getShipDate());
//		setNullableString(pStmt, i++, vo.getDeptCode());
//		setNullableString(pStmt, i++, vo.getTradeTerms());
//		setNullableBigDecimal(pStmt, i++, vo.getExchangeRate());
//		setNullableString(pStmt, i++, vo.getRemark2());
//		setNullableString(pStmt, i++, vo.getProjectCode());
//		setNullableString(pStmt, i++, vo.getDivisionDesc());
//		setNullableString(pStmt, i++, vo.getPreparedBy());
//		setNullableString(pStmt, i++, vo.getAsmForwarder());
//
//		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
//			pStmt.setString(i++, vo.getCompanyCode());
//			pStmt.setString(i++, vo.getPoNo());
//			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
//		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		//setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL delete statement
	* @return        a string of SQL update statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {

//		String sql = "UPDATE T_PO_HDR SET COMPANY_CODE=?, PO_NO=?, SUPL_CODE=?, BILL_CONTACT_PERSON=?, BILL_ADDR1=?, "
//			+ "BILL_ADDR2=?, BILL_ADDR3=?, BILL_ADDR4=?, BILL_TEL=?, BILL_FAX=?, "
//			+ "BILL_EMAIL=?, PO_DATE=?, CUST_CODE=?, SHIP_CONTACT_PERSON=?, SHIP_ADDR1=?, "
//			+ "SHIP_ADDR2=?, SHIP_ADDR3=?, SHIP_ADDR4=?, SHIP_TEL=?, SHIP_FAX=?, "
//			+ "SHIP_EMAIL=?, STATUS=?, SUPL_REF_NO=?, COUNTRY_OF_ORIGIN=?, PORT_OF_LOADING=?, "
//			+ "DESTINATION=?, SHIP_VIA=?, PAY_TERM=?, HANDLED_BY=?, CCY=?, "
//			+ "TOTAL_AMT=?, SHIP_ROUTE=?, FREIGHT_FORWARD=?, REMARK1=?, REVISION=?, "
//			+ "CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE, SO_NO=?, "
//			+ "SHIP_COUNTRY_CODE=?, SHIP_AREA_CODE=?, BILL_COUNTRY_CODE=?, BILL_AREA_CODE=?, SHIP_DATE=?, "
//			+ "DEPT_CODE=?, TRADE_TERMS=?, EXCHANGE_RATE=?, REMARK2=?, PROJECT_CODE=?, "
//			+ "DIVISION_DESC=?, PREPARED_BY=?, ASM_FORWARDER=? "
//			+ "WHERE COMPANY_CODE=? AND PO_NO=? "
//			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

		String sql = "";
		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Constructs a string of SQL delete statement
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		//setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

    /**
    * Constructs a string of SQL update statement and set ACTIVE_IND to NO
    * @return        a string of SQL update statement
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        //String sql = "UPDATE T_PO_HDR SET ACTIVE_IND='" + GENERIC_NO + "', INACTIVE_DATE=SYSDATE, ACTIVE_DATE=NULL WHERE COMPANY_CODE=? AND PO_NO=? ";

    	String sql = "";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {

//    	PurchaseOrderHdrVO vo = (PurchaseOrderHdrVO) obj;
//        int i = 1;
//
//        pStmt.setString(i++, vo.getCompanyCode());
//        pStmt.setString(i++, vo.getPoNo());
    }

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT COMPANY_CODE, PO_NO, PO_DATE, VENDOR_CODE "
			+ "FROM T_PO_HDR "
			+ "WHERE COMPANY_CODE=? AND PO_NO=? "
			+ "ORDER BY COMPANY_CODE, PO_NO";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		PoEnquiryPk pk = (PoEnquiryPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getPoNo());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		return null;	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		PoEnquiryVo vo = new PoEnquiryVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setPoNo(rs.getString("PO_NO"));
		vo.setSuplCode(rs.getString("VENDOR_CODE"));
		vo.setPoDate(CustomSqlDate.customize(rs.getDate("PO_DATE")));
		
//		vo.setBillContactPerson(rs.getString("BILL_CONTACT_PERSON"));
//		vo.setBillAddr1(rs.getString("BILL_ADDR1"));
//		vo.setBillAddr2(rs.getString("BILL_ADDR2"));
//		vo.setBillAddr3(rs.getString("BILL_ADDR3"));
//		vo.setBillAddr4(rs.getString("BILL_ADDR4"));
//		vo.setBillTel(rs.getString("BILL_TEL"));
//		vo.setBillFax(rs.getString("BILL_FAX"));
//		vo.setBillEmail(rs.getString("BILL_EMAIL"));
//		vo.setPoDate(CustomSqlDate.customize(rs.getDate("PO_DATE")));
//		vo.setCustCode(rs.getString("CUST_CODE"));
//		vo.setShipContactPerson(rs.getString("SHIP_CONTACT_PERSON"));
//		vo.setShipAddr1(rs.getString("SHIP_ADDR1"));
//		vo.setShipAddr2(rs.getString("SHIP_ADDR2"));
//		vo.setShipAddr3(rs.getString("SHIP_ADDR3"));
//		vo.setShipAddr4(rs.getString("SHIP_ADDR4"));
//		vo.setShipTel(rs.getString("SHIP_TEL"));
//		vo.setShipFax(rs.getString("SHIP_FAX"));
//		vo.setShipEmail(rs.getString("SHIP_EMAIL"));
//		vo.setStatus(rs.getString("STATUS"));
//		vo.setSuplRefNo(rs.getString("SUPL_REF_NO"));
//		vo.setCountryOfOrigin(rs.getString("COUNTRY_OF_ORIGIN"));
//		vo.setPortOfLoading(rs.getString("PORT_OF_LOADING"));
//		vo.setDestination(rs.getString("DESTINATION"));
//		vo.setShipVia(rs.getString("SHIP_VIA"));
//		vo.setPayTerm(rs.getString("PAY_TERM"));
//		vo.setHandledBy(rs.getString("HANDLED_BY"));
//		vo.setCcy(rs.getString("CCY"));
//		vo.setTotalAmt(CustomBigDecimal.customize(rs.getBigDecimal("TOTAL_AMT")));
//		vo.setShipRoute(rs.getString("SHIP_ROUTE"));
//		vo.setFreightForward(rs.getString("FREIGHT_FORWARD"));
//		vo.setRemark1(rs.getString("REMARK1"));
//		vo.setRevision(rs.getString("REVISION"));
//		vo.setCreateUser(rs.getString("CREATE_USER"));
//		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
//		vo.setUpdateUser(rs.getString("UPDATE_USER"));
//		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
//		vo.setSoNo(rs.getString("SO_NO"));
//		vo.setShipCountryCode(rs.getString("SHIP_COUNTRY_CODE"));
//		vo.setShipAreaCode(rs.getString("SHIP_AREA_CODE"));
//		vo.setBillCountryCode(rs.getString("BILL_COUNTRY_CODE"));
//		vo.setBillAreaCode(rs.getString("BILL_AREA_CODE"));
//		vo.setShipDate(CustomSqlDate.customize(rs.getDate("SHIP_DATE")));
//		vo.setDeptCode(rs.getString("DEPT_CODE"));
//		vo.setTradeTerms(rs.getString("TRADE_TERMS"));
//		vo.setExchangeRate(CustomBigDecimal.customize(rs.getBigDecimal("EXCHANGE_RATE")));
//		vo.setRemark2(rs.getString("REMARK2"));
//		vo.setProjectCode(rs.getString("PROJECT_CODE"));
//		vo.setDivisionDesc(rs.getString("DIVISION_DESC"));
//		vo.setPreparedBy(rs.getString("PREPARED_BY"));
//		vo.setAsmForwarder(rs.getString("ASM_FORWARDER"));

		return vo;
	}

}
